﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ScoreManagement
{
    public partial class ModifyForm : Form
    {
        private string url = "server=127.0.0.1; port=3306;database=exercise;uid=root;pwd=123456";

        public ModifyForm()
        {
            InitializeComponent();
        }

        private void ModifyForm_Load(object sender, EventArgs e)
        {
            string _sql = "SELECT s_id as'学号',s_name as '姓名',s_class as '专业',s_sex as '性别',s_age as '出生日期',s_Allscore as '总学分',s_about as '备注' FROM student";
            MySqlConnection conn = new MySqlConnection(url);
            MySqlDataAdapter sda = new MySqlDataAdapter(_sql, conn);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            stuDGV.DataSource = ds.Tables[0].DefaultView;
        }

        private void stuDGV_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            //获得选中的记录行
            DataGridViewRow dgvRow = stuDGV.Rows[e.RowIndex];
            //获得行单元格集合
            DataGridViewCellCollection dgvCC = dgvRow.Cells;
            //获得单元格数据
            stuXH.Text = dgvCC[0].Value.ToString();
            stuXM.Text = dgvCC[1].Value.ToString();
            stuZY.Text = dgvCC[2].Value.ToString();
            if (dgvCC[3].Value.ToString() == "男")
            {
                male.Checked = true;
            }
            else
            {
                female.Checked = true;
            }
            stuCS.Text = Convert.ToDateTime(dgvCC[4].Value).ToShortDateString();
            stuZXF.Text = dgvCC[5].Value.ToString();
            stuBZ.Text = dgvCC[6].Value.ToString();
        }

        private void stuUpdate_Click(object sender, EventArgs e)
        {
            string _sql = "SELECT count(*) FROM student WHERE s_id='" + stuXH.Text + "'";
            MySqlConnection conn = new MySqlConnection(url);
            MySqlCommand cmd = new MySqlCommand(_sql, conn);
            //检查是否有此学生记录，有就修改，无则添加
            try
            {
                conn.Open();
                long cnt = (long)cmd.ExecuteScalar();
                string sex = male.Checked ? "男" : "女";
                //修改记录
                if (cnt > 0)
                {
                    _sql = "UPDATE student SET s_name ='" + stuXM.Text + "',s_sex='" + sex + "',s_age='" + stuCS.Text +
                       "',s_class='" + stuZY.Text + "',s_Allscore='" + stuZXF.Text+ "',s_about='" +
                       stuBZ.Text + "'WHERE s_id='" + stuXH.Text + "'";
                }
                //添加新记录
                else
                {
                    _sql = "insert into student(s_id,s_name,s_sex,s_age,s_class,s_Allscore,s_about) values('" + stuXH.Text + "','" + stuXM.Text + "','" + sex + "','" +
                        stuCS.Text + "','" + stuZY.Text + "','" + stuZXF.Text + "','" + stuBZ.Text + "')";
                }
                cmd = new MySqlCommand(_sql, conn);
                cmd.ExecuteNonQuery();
                ModifyForm_Load(null, null);
            }
            finally
            {
                conn.Close();
            }
        }

        private void stuDetele_Click(object sender, EventArgs e)
        {
            DialogResult ret = MessageBox.Show("确定要删除记录吗？", "删除", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
            if (ret == DialogResult.Cancel)
            {
                return;
            }
            string _sql = "delete from student where s_id='" + stuXH.Text + "'";
            MySqlConnection conn = new MySqlConnection(url);
            MySqlCommand cmd = new MySqlCommand(_sql, conn);
            try
            {
                conn.Open();
                int rows = cmd.ExecuteNonQuery();
                ModifyForm_Load(null, null);
                if (rows == 1)
                {
                    MessageBox.Show("删除成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
